//
//  EasyNoteDBManger.m
//  todoApp
//
//  Created by wangyaqing on 15/3/24.
//  Copyright (c) 2015年 ll studio. All rights reserved.
//

#import "NoteDBManger.h"
#import "NoteModel.h"
#import <FMDB/FMDB.h>

#ifdef DEBUG
#define ENLog(fmt, ...) NSLog((@"%s [Line %d] " fmt), __PRETTY_FUNCTION__, __LINE__, ##__VA_ARGS__);
#else
#define ENLog(...)
#endif

static  NSString *DBFileName = @"easyNote.sqlite";
static  NSString *DBNoteTableName = @"easyNoteTable";

@implementation NoteDBManger

+ (NSString*)databasePathString
{
    static NSString *filePath = nil;

    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        NSString *documentFolder = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
        filePath = [documentFolder stringByAppendingPathComponent:DBFileName];
    });
    return filePath;
}

+ (instancetype)shareInstance
{
    static NoteDBManger *instance = nil;
    
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[NoteDBManger alloc]init];
    });
    
    return instance;
}

- (FMDatabase *)dataBase
{
    if (!_dataBase) {
        
        _dataBase = [FMDatabase databaseWithPath:[NoteDBManger databasePathString]];
        
        if (_dataBase) {
            if ([_dataBase open]) {
                
                if (![_dataBase tableExists:DBNoteTableName]) {
                    //create table for notes, noteId, noteText, noteTag, userId
                    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE '%@' (noteId TEXT, noteText TEXT, noteTag TEXT, userId TEXT, localCreateTime DOUBLE)", DBNoteTableName];
                    BOOL ret =  [_dataBase executeUpdate:sql];
                    ENLog(@"create Table %@", ret?@"success":@"failed");
                }
            }
            else
            {
                ENLog(@"open database failed");
            }
        }
        else
        {
            ENLog(@"init dataBase failed");
        }
    }
    
    return _dataBase;
}

- (void)closeDataBase
{
    if (self.dataBase) {
        [self.dataBase close];
    }
}

- (void)dealloc
{
    [self closeDataBase];
}

- (BOOL)addNote:(NoteModel *)note 
{
    [self.dataBase beginTransaction];
    BOOL isSuccess = NO;
    
    @try {
        
        NSTimeInterval interval = note.localCreateTime;
        
        if (interval == 0) {
            interval = [[NSDate date]timeIntervalSince1970];
        }
        
        NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (noteId, noteText, localCreateTime) values ('%@', '%@', '%@')",DBNoteTableName, note.noteId, note.noteText, [NSNumber numberWithDouble: interval]];
        
        isSuccess = [self.dataBase executeUpdate:sql];
        
        if (isSuccess) {
            
            note.localCreateTime = interval;
            
            if (note.noteTag.length > 0) {
                sql = [NSString stringWithFormat:@"UPDATE %@ SET noteTag = '%@' WHERE noteId = '%@'", DBNoteTableName, note.noteTag, note.noteId];
                [self.dataBase executeUpdate:sql];
            }
            
            if (note.userId.length > 0) {
                sql = [NSString stringWithFormat:@"UPDATE %@ SET userId = '%@' WHERE noteId = '%@'", DBNoteTableName, note.userId, note.noteId];
                [self.dataBase executeUpdate:sql];
            }
        }
        
    }
    @catch (NSException *exception) {
        [self.dataBase rollback];
    }
    @finally {
        if (isSuccess) {
            [self.dataBase commit];
        }
        else
        {
            [self.dataBase rollback];
        }
    }
    
    return isSuccess;
}

- (void)addNotes:(NSArray *)notes
{
    if (![notes isKindOfClass:[NSArray class]] || notes.count == 0) {
        return;
    }
    
    for (NoteModel *note in notes) {
    
        if (note.noteText.length == 0) {
            continue;
        }
        [self addNote:note];
    }
}

- (void)deleteAllNotes
{
    NSString *SQL = [NSString stringWithFormat:@"DELETE FROM %@", DBNoteTableName];
    [self.dataBase executeUpdate:SQL];
}

- (void)deleleNotes:(NSArray *)notes
{
    if (![notes isKindOfClass:[NSArray class]] || notes.count == 0) {
        return;
    }
    
    for (NoteModel *note in notes) {
        
        NSString *deleteSql = [NSString stringWithFormat:
                               @"DELETE FROM %@ WHERE noteId = '%@'",
                               DBNoteTableName, note.noteId];
        
        [self.dataBase executeUpdate:deleteSql];
    }
}

- (NoteModel *)queryNoteWithId:(NSString *)noteId
{
    if (!noteId) {
        return nil;
    }
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE noteId = '%@'", DBNoteTableName, noteId];
    
    FMResultSet *result = [self.dataBase executeQuery:sql];
    
    if ([result columnCount] > 0)
    {
        if ([result next]) {
            NoteModel *note = [[NoteModel alloc]init];
            note.noteId = [result stringForColumn:@"noteId"];
            note.noteText = [result stringForColumn:@"noteText"];
            note.noteTag = [result stringForColumn:@"noteTag"];
            note.userId = [result stringForColumn:@"userId"];
            note.localCreateTime  = [result doubleForColumn:@"localCreateTime"];
            
            return note;
        }
    }
    return nil;
}

- (NSMutableArray *)queryAllNotes
{
    NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY localCreateTime DESC", DBNoteTableName];
    
    FMResultSet *result = [self.dataBase executeQuery:querySql];
    
    if ([result columnCount] > 0)
    {
        NSMutableArray *rets = [[NSMutableArray alloc]init];
        
        while ([result next]) {
            NoteModel *note = [[NoteModel alloc]init];
            note.noteId = [result stringForColumn:@"noteId"];
            note.noteText = [result stringForColumn:@"noteText"];
            note.noteTag = [result stringForColumn:@"noteTag"];
            note.userId = [result stringForColumn:@"userId"];
            note.localCreateTime = [result doubleForColumn:@"localCreateTime"];
         
            [rets addObject:note];
        }
        
        return rets;
    }
    else
    {
        return [@[]mutableCopy];
    }
    
}

- (NSMutableArray *)queryAllTags
{
    NSString *querySql = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY localCreateTime DESC", DBNoteTableName];
    
    FMResultSet *result = [self.dataBase executeQuery:querySql];
    
    if ([result columnCount] > 0)
    {
        NSMutableArray *rets = [[NSMutableArray alloc]init];
        NSInteger index = 0;
        while ([result next]) {
//            NoteModel *note = [[NoteModel alloc]init];
//            note.noteId = [result stringForColumn:@"noteId"];
//            note.noteText = [result stringForColumn:@"noteText"];
//            note.noteTag = [result stringForColumn:@"noteTag"];
//            note.userId = [result stringForColumn:@"userId"];
//            note.localCreateTime = [result doubleForColumn:@"localCreateTime"];
            if (index == 0) {
                if ([result stringForColumn:@"noteTag"] != nil) {
                    [rets addObject:[result stringForColumn:@"noteTag"]];
                    index++;
                }
            }
            
            for (int i=0; i<rets.count; i++) {
                if ([rets[i] isEqualToString:[result stringForColumn:@"noteTag"]]) {
                    break;
                }else if (i == rets.count - 1)
                {
                    if ([result stringForColumn:@"noteTag"] != nil) {
                        [rets addObject:[result stringForColumn:@"noteTag"]];
                    }
                }
            }
        }
        
        return rets;
    }
    else
    {
        return [@[]mutableCopy];
    }
}


- (NSMutableArray *)queryAllNotesForUser:(NSString *)userId
{
    if (!userId) {
        return [self queryAllNotes];
    }
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE userId = %@", DBNoteTableName, userId];
    
    FMResultSet *result = [self.dataBase executeQuery:sql];
    
    if ([result columnCount] > 0)
    {
        NSMutableArray *rets = [[NSMutableArray alloc]init];
        
        while ([result next]) {
            NoteModel *note = [[NoteModel alloc]init];
            note.noteId = [result stringForColumn:@"noteId"];
            note.noteText = [result stringForColumn:@"noteText"];
            note.noteTag = [result stringForColumn:@"noteTag"];
            note.userId = [result stringForColumn:@"userId"];
            note.localCreateTime = [result doubleForColumn:@"localCreateTime"];

            [rets addObject:note];
        }
        
        return rets;
    }
    else
    {
        return [@[]mutableCopy];
    }
}

- (NSMutableArray *)queryNotesForTag:(NSString *)tag
{
    if (!tag) {
        return [@[]mutableCopy];
    }
    
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE noteTag = '%@'", DBNoteTableName, tag];
    
    FMResultSet *result = [self.dataBase executeQuery:sql];
    
    if ([result columnCount] > 0)
    {
        NSMutableArray *rets = [[NSMutableArray alloc]init];
        
        while ([result next]) {
            NoteModel *note = [[NoteModel alloc]init];
            note.noteId = [result stringForColumn:@"noteId"];
            note.noteText = [result stringForColumn:@"noteText"];
            note.noteTag = [result stringForColumn:@"noteTag"];
            note.userId = [result stringForColumn:@"userId"];
            note.localCreateTime = [result doubleForColumn:@"localCreateTime"];
            [rets addObject:note];
        }
        
        return rets;
    }
    else
    {
        return [@[]mutableCopy];
    }
}


- (BOOL)updateNote:(NoteModel *)note
{
    if (![note isKindOfClass:[NoteModel class]] || !note.noteId || (note.noteText.length == 0)) {
        return NO;
    }
    
    [self.dataBase beginDeferredTransaction];
    
    BOOL success = NO;
    
    @try {
        
        NSString *sql = nil;
        note.localCreateTime = [[NSDate date]timeIntervalSince1970];
        
        sql = [NSString stringWithFormat:@"UPDATE %@ SET noteText = '%@',localCreateTime = '%@'  WHERE noteId = '%@'", DBNoteTableName, note.noteText, [NSNumber numberWithDouble:note.localCreateTime], note.noteId];
        
        success =  [self.dataBase executeUpdate:sql];
        
        sql = [NSString stringWithFormat:@"UPDATE %@ SET noteTag = '%@' WHERE noteId = '%@'", DBNoteTableName, note.noteTag, note.noteId];
        success =  [self.dataBase executeUpdate:sql];
        
        if (note.userId.length > 0) {
            sql = [NSString stringWithFormat:@"UPDATE %@ SET userId = '%@' WHERE noteId = '%@'", DBNoteTableName, note.userId, note.noteId];
            success = [self.dataBase executeUpdate:sql];
        }
        
    }
    @catch (NSException *exception) {
        [self.dataBase rollback];
    }
    @finally {
        if (success) {
            [self.dataBase commit];
        }
        else
        {
            [self.dataBase rollback];
        }
    }
    
    return success;

}

@end
